package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import conn.Conn;
import bean.*;

public class TeacherSql {

	//查询是否有该教师号密码的教师
	public static boolean isTeacher(String unumber, String pwd){

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String type = null;

		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID =" + "'" + unumber+ "'" + " and Password=" + "'" + pwd + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				type = rs.getString("Type");
				return type.equals("teacher");
			}
			return false;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return false;
	}

	//根据教师号查询教师姓名
	public static String getName(String unumber) {

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = conn.createStatement();
			String sql = "select Name from customer where ID = " + "'" + unumber +"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getString("Name");
			}
			return null;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//查询个人信息
	public static Customer getCustomerByID(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID = "+"'" + ID + "'";
			rs = stmt.executeQuery(sql);
			Customer customer = null;
			if(rs.next()) {
				String Name = rs.getString("Name");
				String Email = rs.getString("Email");
				String Sex = rs.getString("Sex");
				String Phone = rs.getString("Phone");
				String Type = rs.getString("Type");
				customer = new Customer(Name, ID);
				customer.setSex(Sex);
				customer.setEmail(Email);
				customer.setPhone(Phone);
				customer.setType(Type);
				return customer;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//查询教授学生信息
	public static ArrayList<Score> GetScore(String T_Name){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Score> list = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			String sql="select * from score where T_Name = '" + T_Name +"'";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				String ID = rs.getString("ID");
				String Name = rs.getString("Name");
				String Title = rs.getString("Title");
				String Score = rs.getString("Score");
				String Grade = rs.getString("Grade");
				Score score = new Score(ID, Name, Title,T_Name, Score ,Grade);
				list.add(score);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	//查询教授学生信息
	public static ArrayList<GradeDesign> GetGradeDesign(String T_Name){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<GradeDesign> list = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			String sql="select * from grade_design where T_Name = '" + T_Name +"'";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				String ID = rs.getString("ID");
				String Name = rs.getString("Name");
				String RWS = rs.getString("RWS");
				String KTBG = rs.getString("KTBG");
				String BYLW = rs.getString("BYLW");
				String CODE = rs.getString("CODE");
				GradeDesign gradeDesign = new GradeDesign(ID,Name,RWS,KTBG,BYLW,CODE);
				list.add(gradeDesign);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	//查询特定ID的Score信息
	public static Score getScoreByID(String ID){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		Score score  = new Score();
		try {
			stmt = conn.createStatement();
			String sql="select * from score where ID = '" + ID +"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				String Name = rs.getString("Name");
				String Title = rs.getString("Title");
				String Score = rs.getString("Score");
				String Grade = rs.getString("Grade");
				score = new Score(ID, Name, Title, Score ,Grade);
				return score;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	//更新特定ID的Score信息
	public static boolean updateScore(String ID,String Score,String Grade){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "update score set Score = '" + Score + "',Grade = '" +  Grade + "'where ID = '"+ ID +"'";
			stmt.executeUpdate(sql);
			return true;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

//	更新教师信息
	public static boolean updateCustomer(String ID, String Email,String Sex,String Phone){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set Email = '" + Email + "',Sex = '" +  Sex + "',Phone = '" + Phone + "'where ID = '"  + ID + "'";
			stmt.executeUpdate(sql);
			return true;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

	//更新密码
	public static boolean updatePassword(String ID, String Password){

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set password = " + "'" + Password + "'" +  " where ID = " + "'" + ID + "'";
			a = stmt.executeUpdate(sql);
			return a > 0;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

}